import openpyxl

"""
一键导出某学院所有同学的竞赛报表，包括竞赛名称，等级，作品名称，指导老师，负责人，队员，获奖级别，时间
"""

target_names = ['竞赛名称', '赛事等级', '作品名称', '指导老师', '负责人', '队员', '获奖级别', '获奖时间']

def save_excel(data_list, output_file_name, names=target_names):
    if not output_file_name.endswith('.xlsx'):
        output_file_name += ".xlsx"
    # workbook
    wb = openpyxl.Workbook()
    # worksheet
    ws = wb.active
    title = names
    for i in range(len(title)):
        ws.cell(row=1, column=i+1).value = title[i]
    for i in range(len(data_list)):
        for j in range(len(names)):
            if j == 3 or j == 5:
                count = len(data_list[i][names[j]])
                ws.cell(row=i+2, column=j+1).value = ' '
                for k in range(count):
                    ws.cell(row=i+2, column=j+1).value += data_list[i][names[j]][k] + ' '
            else:
                ws.cell(row=i+2, column=j+1).value = data_list[i][names[j]]
    wb.save(filename=output_file_name)



if __name__ == '__main__':
    # 指导老师与队员为list
    target_data = [
        {
            target_names[0]: '大计赛',
            target_names[1]: '一级',
            target_names[2]: '极星-你的竞赛好帮手',
            target_names[3]: ['李众', '李华玲'],
            target_names[4]: '赵宇宁',
            target_names[5]: ['张全璞', '党男男'],
            target_names[6]: '省级一等奖',
            target_names[7]: '2022.7.1'
        },
        {
            target_names[0]: '大计赛',
            target_names[1]: '一级',
            target_names[2]: '极星-你的竞赛好帮手',
            target_names[3]: ['李众', '李华玲'],
            target_names[4]: '赵宇宁',
            target_names[5]: ['张全璞', '党男男'],
            target_names[6]: '省级一等奖',
            target_names[7]: '2022.7.1'
        }
    ]
    output_file_name = 'data.xlsx'
    save_excel(target_data, output_file_name, target_names)
